package com.kelly.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author guofangbi
 *
 */
public class ReadExcel {

	/**
	 * 读取excel工具
	 * 
	 * @param excel的绝对路径，包括文件名。如：D:/document/bizi.xls
	 * @param sheetNo
	 *            excel的sheet编号，从0开始
	 * @return 传回的数据为所有行的list，每一行是每个单元格的list
	 */
	public static List<List<String>> readExcel(String filePath, int sheetNo) {
		String fileType = filePath.substring(filePath.lastIndexOf(".")+1);
		InputStream is = null;
		Workbook wb = null;
		try {
			is = new FileInputStream(filePath);
			if("xls".equalsIgnoreCase(fileType)){
				wb = new HSSFWorkbook(is);
			}else{
				wb = new XSSFWorkbook(is);
			}
			
			Sheet sheet = wb.getSheetAt(sheetNo);

			List<List<String>> result = new ArrayList<>();
			for (int i = 0; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				List<String> cellList = new ArrayList<>();
				for (int j = 0; j <= row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j);
					cellList.add(ReadCellValue.readCellValue(cell));
				}
				result.add(cellList);
			}
			return result;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if(wb!=null){
				try {
					wb.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
			if(is!=null){
				try {
					is.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
		}
		return null;
	}

}
